﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Library.Entity;
using Library.Connection;
using System.Data.SqlClient;
using Library.SQL;
using System.Collections;
using System.Data;
using MySql.Data.MySqlClient;

namespace Library.Services
{
    public class BookService
    {
        DataBaseConnectivity m_dataBaseConectivity = new DataBaseConnectivity();
        BookStockService m_BookStockService = new BookStockService();
        public BookService()
        { }
        internal Book GetBookById(int Id)
        {
            Book result = null;
            try
            {
                m_dataBaseConectivity.MySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.SelectBookById(), m_dataBaseConectivity.MySqlConnection);
                cmd.Parameters.Add("Id", MySqlDbType.Int32).Value = Id;
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new Book(rd);
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection.Close();
            }
            return result;
        }
        internal Book GetBookByBookCode(string BookCode)
        {
            Book result = null;
            try
            {
                m_dataBaseConectivity.MySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.SelectBookByCode(), m_dataBaseConectivity.MySqlConnection);
                cmd.Parameters.Add("Code", MySqlDbType.VarChar).Value = BookCode;
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new Book(rd);
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection.Close();
            }
            return result;
        }

        internal void InsertLog(Book m_Book, string p)
        {
          
        }

        internal IList GetAllBook()
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.MySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.SelectAllBook(), m_dataBaseConectivity.MySqlConnection);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new Book(rd));
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection.Close();
            }
            return result;
        }

        internal void InsertNewBook(Book Book)
        {
            try
            {
                m_dataBaseConectivity.MySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.InsertBook(), m_dataBaseConectivity.MySqlConnection);

                cmd.Parameters.Add("Code", MySqlDbType.VarChar).Value = Book.Code;
                cmd.Parameters.Add("Name", MySqlDbType.VarChar).Value = Book.Name;
                cmd.Parameters.Add("Remark", MySqlDbType.VarChar).Value = Book.Remark;
                cmd.Parameters.Add("CreatedDate", MySqlDbType.Datetime).Value = DateTime.Now;
                cmd.Parameters.Add("ModifiedDate", MySqlDbType.Datetime).Value = DateTime.Now;
                cmd.Parameters.Add("CreatedUserId", MySqlDbType.Int32).Value = Book.CreatedUserId;
                cmd.Parameters.Add("ModifiedUserId", MySqlDbType.Int32).Value = Book.ModifiedUserId;

                cmd.Parameters.Add("BookShelfId", MySqlDbType.Int32).Value = Book.BookShelfId;
                cmd.Parameters.Add("BookCategoryId", MySqlDbType.Int32).Value = Book.BookCategoryId;
                cmd.Parameters.Add("Barcode", MySqlDbType.VarChar).Value = Book.Barcode;
                cmd.Parameters.Add("Author", MySqlDbType.VarChar).Value = Book.Author;
                cmd.Parameters.Add("Publisher", MySqlDbType.VarChar).Value = Book.Publisher;
                cmd.Parameters.Add("DDC", MySqlDbType.VarChar).Value = Book.DDC;
                cmd.Parameters.Add("ISBN", MySqlDbType.VarChar).Value = Book.ISBN;

                cmd.ExecuteNonQuery();
                cmd.CommandText = SQLScript.SelectMaxBookId();
                cmd.Parameters.Clear();
                Book.Id = Convert.ToInt32(cmd.ExecuteScalar());

                BookStock bs = new BookStock();
                bs.BookBalance = 0;
                bs.BookId = Book.Id;
                bs.Code = Book.Code;
                bs.CreatedUserId = Book.CreatedUserId;
                bs.ModifiedUserId = Book.ModifiedUserId;
                bs.Name = Book.Name;
                m_BookStockService.InsertNewBookStock(bs);
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection.Close();
            }
        }

        internal void UpdateBook(Book Book)
        {
            try
            {
                m_dataBaseConectivity.MySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.UpdateBook(), m_dataBaseConectivity.MySqlConnection);

                cmd.Parameters.Add("Code", MySqlDbType.VarChar).Value = Book.Code;
                cmd.Parameters.Add("Name", MySqlDbType.VarChar).Value = Book.Name;
                cmd.Parameters.Add("Remark", MySqlDbType.VarChar).Value = Book.Remark;
                cmd.Parameters.Add("ModifiedDate", MySqlDbType.Datetime).Value = DateTime.Now;
                cmd.Parameters.Add("ModifiedUserId", MySqlDbType.Int32).Value = Book.ModifiedUserId;
                cmd.Parameters.Add("BookShelfId", MySqlDbType.Int32).Value = Book.BookShelfId;
                cmd.Parameters.Add("BookCategoryId", MySqlDbType.Int32).Value = Book.BookCategoryId;
                cmd.Parameters.Add("Barcode", MySqlDbType.VarChar).Value = Book.Barcode;
                cmd.Parameters.Add("Author", MySqlDbType.VarChar).Value = Book.Author;
                cmd.Parameters.Add("Publisher", MySqlDbType.VarChar).Value = Book.Publisher;
                cmd.Parameters.Add("DDC", MySqlDbType.VarChar).Value = Book.DDC;
                cmd.Parameters.Add("ISBN", MySqlDbType.VarChar).Value = Book.ISBN;
                cmd.Parameters.Add("Id", MySqlDbType.Int32).Value = Book.Id;
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection.Close();
            }
        }

        internal void DeleteBook(Book Book)
        {
            try
            {
                m_dataBaseConectivity.MySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.DeleteBook(), m_dataBaseConectivity.MySqlConnection);
                cmd.Parameters.Add("Id", MySqlDbType.Int32).Value = Book.Id;
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection.Close();
            }
        }
        internal int GetMaxBookId()
        {
            int result = 0;
            try
            {
                m_dataBaseConectivity.MySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.SelectMaxBookId(), m_dataBaseConectivity.MySqlConnection);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                object r = cmd.ExecuteScalar();
                result = Convert.ToInt32(r is DBNull ? 0 : r);
                return result;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection.Close();
            }
        }
        internal string GenerateCode()
        {
            string result = "BOOK000001";
            try
            {
                int maxMemberId = GetMaxBookId();
                Book latestUser = GetBookById(maxMemberId);
                if (latestUser == null)
                    return result;
                string lastCOde = latestUser.Code;

                int j = 0;
                for (int i = lastCOde.Length; i > -1; i--)
                {
                    string chr = lastCOde.Substring(i - 1, 1);
                    int p = 0;
                    if (!int.TryParse(chr, out p))
                    {
                        j = i;
                        break;
                    }
                }
                string codeInt = lastCOde.Substring(j, lastCOde.Length - j);
                int pad = codeInt.Length;
                int code = Convert.ToInt32(codeInt);
                int nextCode = code + 1;
                string prefix = lastCOde.Substring(0, j);
                result = prefix + nextCode.ToString().PadLeft(pad, '0');

                Book found = GetBookByBookCode(result);
                while (found != null)
                {
                    nextCode++;
                    result = prefix + nextCode.ToString().PadLeft(pad, '0');
                    found = GetBookByBookCode(result);
                }

                return result;
            }
            catch (Exception x)
            {
                throw x;
            }
        }

        internal IList GetBookByLike(string p)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.MySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.SelectAllBookByLike(p), m_dataBaseConectivity.MySqlConnection);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new Book(rd));
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection.Close();
            }
            return result;
        }
    }
}